package com.ydd.app.dao.impl;

import java.util.List;

import com.ydd.app.bean.common.DispalyBean;
import com.ydd.common.util.CommonUtil;


public class CompanyDaoSql {
	
	public static String QUERY_CONFIG_SQL = 
			" SELECT  SysID "
            +" ,Names "
            +" ,SendID "
            +" FROM TBL_ConfigType ";
	
	public static String QUERY_COMPANY_TYPES_SQL = 
			" SELECT company_type_id "
            +",company_type_name "
            +"FROM  company_type_mst order by sort_value desc ";
	
	public static String queryCompanys(final int page,final int pageCount,final String companyTypeId) {
		final StringBuilder sb = new StringBuilder();
		sb.append(" SELECT  top " +pageCount 
			+ " c.SysID "
//			+" ,c.CompanyName"
			+", c.CompanyTitle  "
//			+" ,c.nDate "
//			+" ,c.legalPerson "
//			+" ,c.Totalsharecapital "
//			+" ,c.Adress "
//			+" ,c.ContactTele "
//			+" ,c.Ctype "
//			+" ,c.Nature "
//			+" ,c.CScale "
//			+" ,c.Mainbusiness "
//			+" ,c.Mainproducts "
			+" ,c.Ownedindustry "
			+" ,c.OwnedindustryStr "
//			+" ,c.Ntype "
//			+" ,c.Condition "
//			+" ,c.Intentional "
			+" ,c.Policyneeds "
//			+" ,c.needsconfig "
//			+" ,c.department "
//			+" ,c.departmentOper "
//			+" ,c.departmentTele "
//			+" ,c.departmentEmail "
//			+" ,c.WriteOper "
//			+" ,c.WriteTele "
			+" ,WriteDate "
//			+" ,c.IsExamine "
//			+" ,c.IsYesOrNo "
//			+" ,c.ExamineOper "
			+" ,c.ExamineDate "
//			+" ,c.DeleteRecord "
//			+" ,c.SendID "
//      		+" ,c.Complete "
//      		+" ,c.CompleteUnit "
//      		+" ,c.CompleteMoney "
      		+" ,c.CompleteDate "
//      		+" ,c.SystemOper "
      		+" ,c.SystemDate "
      		+" FROM TBL_CompanyInfo as c  "
      		+" where  c.DeleteRecord = '0' ");
		    if( 0 != page ) {
				sb.append("   and c.SysID not in ( ");
				sb.append(" select " 
						  +"    top  "+ (pageCount * page)
						  +" SysID  "
			              +" FROM  "
			              +" TBL_CompanyInfo   " 
			              +" where " 
			              +" DeleteRecord = '0'  ");
				if( CommonUtil.isNotEmpty(companyTypeId)) {
					sb.append(" and ( select count(*) from dbo.func_splitid(c.Ownedindustry,',') where value = "+companyTypeId+" ) > 0 ");
				}
				sb.append(" order by SystemDate desc) ");
		    }
		    if( CommonUtil.isNotEmpty(companyTypeId)) {
				sb.append("  and ( select count(*) from dbo.func_splitid(c.Ownedindustry,',') where value = "+companyTypeId+" ) > 0  ");
			}
			sb.append(" order by c.SystemDate desc ");
		    
		return sb.toString();
	}
	
	/**
	 * 根据 id 查询 Company  
	 */
	public static String queryCompanyDetailById() {
		final StringBuilder sb = new StringBuilder();
		sb.append(" SELECT " 
				+ " c.SysID "
				+" ,c.CompanyName,"
				+" c.CompanyTitle  "
				+" ,c.nDate "
				+" ,c.legalPerson "
				+" ,c.Totalsharecapital "
				+" ,c.Adress "
				+" ,c.ContactTele "
				+" ,c.Ctype "
				+" ,c.Nature "
				+" ,c.CScale "
				+" ,c.Mainbusiness "
				+" ,c.Mainproducts "
				+" ,c.Ownedindustry "
				+" ,c.OwnedindustryStr "
				+" ,c.Ntype "
				+" ,c.Condition "
				+" ,c.Intentional "
				+" ,c.Policyneeds "
				+" ,c.needsconfig "
				+" ,c.department "
				+" ,c.departmentOper "
				+" ,c.departmentTele "
				+" ,c.departmentEmail "
				+" ,c.WriteOper "
				+" ,c.WriteTele "
				+" ,WriteDate "
				+" ,c.IsExamine "
				+" ,c.IsYesOrNo "
				+" ,c.ExamineOper "
				+" ,c.ExamineDate "
				+" ,c.DeleteRecord "
				+" ,c.SendID "
	      		+" ,c.Complete "
	      		+" ,c.CompleteUnit "
	      		+" ,c.CompleteMoney "
	      		+" ,c.CompleteDate "
	      		+" ,c.SystemOper "
	      		+" ,c.SystemDate "
	      		+" FROM TBL_CompanyInfo as c  "
      		+" where "
      		+" c.SysID = ? "
      		+" and c.DeleteRecord = '0' " );
		return sb.toString();
	}
	
	public static String queryInDispalyCompanyDetailSql(final List<DispalyBean> types){
		final StringBuffer sb = new StringBuffer();
		String sign = "";
		sb.append(" select ");
		for (DispalyBean dispalyBean : types) {
			if( null != dispalyBean && CommonUtil.isNotEmpty(dispalyBean.getFieldName())) {
				sb.append(sign);
				if( "1".equals(dispalyBean.getDisplay())) {
					sb.append( dispalyBean.getFieldName());
				}
				else {
					sb.append( " '' as "+dispalyBean.getFieldName());
				}
				sign = ",";
			}
		}
		sb.append(" from TBL_CompanyInfo where SysID = ?  and  DeleteRecord = '0'  ");
		return sb.toString();
	}
	
	
	public static String queryDispalySql(){
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT FieldName "
				+" ,DisplayName "
				+" ,Display "
				+" FROM DisplayField ");
		return sb.toString();
	}
	
	public final static String INSERT_COMPANY_SQL = 
			" insert into TBL_CompanyInfo ( "
			+" SysID, "
			+" CompanyName, "
			+" nDate, "
			+" legalPerson, "
			+" Totalsharecapital, "
			+" Adress, "
			+" ContactTele, "
			+" Ctype, "
			+" Nature, "
			+" CScale, "
			+" Mainbusiness, "
			+" Mainproducts, "
			+" Ownedindustry, "
			+" OwnedindustryStr, "
			+" Ntype, "
			+" Condition, "
			+" Intentional, "
			+" Policyneeds, "
			+" needsconfig, "
			+" department, "
			+" departmentOper, "
			+" departmentTele, "
			+" departmentEmail, "
			+" WriteOper, "
			+" WriteTele, "
			+" WriteDate, "
			+" IsExamine, "
			+" IsYesOrNo, "
			+" ExamineOper, "
			+" ExamineDate, "
			+" DeleteRecord, "
			+" SendID, "
			+" Complete, "
			+" CompleteUnit, "
			+" CompleteMoney, "
			+" CompleteDate, "
			+" SystemOper, "
			+" SystemDate, "
			+" CompanyTitle) "
			+" values(?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?)";
	
	public final static String UPDATE_COMPANY_SQL = 
			" update TBL_CompanyInfo set "
			+" CompanyName= ?, "
			+" nDate= ?, "
			+" legalPerson= ?, "
			+" Totalsharecapital= ?, "
			+" Adress= ?, "
			+" ContactTele= ?, "
			+" Ctype= ?, "
			+" Nature= ?, "
			+" CScale= ?, "
			+" Mainbusiness= ?, "
			+" Mainproducts= ?, "
			+" Ownedindustry= ?, "
			+" OwnedindustryStr= ?, "
			+" Ntype= ?, "
			+" Condition= ?, "
			+" Intentional= ?, "
			+" Policyneeds= ?, "
			+" needsconfig= ?, "
			+" department= ?, "
			+" departmentOper= ?, "
			+" departmentTele= ?, "
			+" departmentEmail= ?, "
			+" WriteOper= ?, "
			+" WriteTele= ?, "
			+" WriteDate= ?, "
			+" IsExamine= ?, "
			+" IsYesOrNo= ?, "
			+" ExamineOper= ?, "
			+" ExamineDate= ?, "
			+" DeleteRecord= ?, "
			+" SendID= ?, "
			+" Complete= ?, "
			+" CompleteUnit= ?, "
			+" CompleteMoney= ?, "
			+" CompleteDate= ?, "
			+" SystemOper= ?, "
			+" SystemDate= ?, "
			+" CompanyTitle = ? "
			+"  where SysID = ? ";
	
	
	public final static String INSERT_CONFIG_TYPE_SQL = 
			" insert into TBL_ConfigType ( "
			+" SysID, "
			+" Names, "
			+" SendID) "
			+" values(?,?,?)";
	
	public final static String UPDATE_CONFIG_TYPE_SQL = 
			" update TBL_ConfigType set "
			+" Names = ? , "
			+" SendID = ? "
			+" where SysID = ? ";
	
	public final static String UPDATE_DISPALY_TYPE_SQL = 
			" update DisplayField set  "
			+" DisplayName = ? , "
			+" Display = ?, "
			+" Visible = ?  "
			+" where FieldName = ? ";
	
	public final static String INSERT_DISPALY_TYPE_SQL = 
			" insert into  DisplayField(  "
			+" DisplayName, "
			+" Display, "
			+" Visible,FieldName)"
			+" values(?,?,?,?) ";
}
